# coding: utf-8

import time
import requests
import pymysql

from log import logger

url = 'http://i.sporttery.cn/odds_calculator/get_proportion?i_format=json&pool[]=had&pool[]=hhad'
HEADER = {
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 '
    '(KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36'
}

def main():
    # 打开数据库连接
    logger.info('Mysql DB connect...')
    db = pymysql.connect("120.92.172.149", "root", "XdrgBnjI87", "lottery", use_unicode=True, charset="utf8")
    cursor = db.cursor()

    while True:
        try:
            logger.info('Get URL:%s' % url)
            r = requests.get(url, headers=HEADER)
            break
        except:
            time.sleep(5)
            pass

    datas = r.json()['data']
    for key in datas:
        logger.info('data:%s' % key)

        if 'had' in datas[key]:
            type = datas[key]['had']['type']
            num = datas[key]['had']['num']
            mid = datas[key]['had']['mid']
            win = datas[key]['had']['win']
            lose = datas[key]['had']['lose']
            draw = datas[key]['had']['draw']
            total = datas[key]['had']['total']

            sql = """INSERT INTO tb_vote_rate
              (`type`, `num`, `mid`, `win`, `lose`, `draw`, `total`)
              VALUES ('%s','%s','%s','%s','%s','%s','%s')""" % (type, num, mid, win, lose, draw, total)

            try:
                logger.info('execue SQL:%s' % sql)
                # 执行sql语句
                cursor.execute(sql)
                # 提交到数据库执行
                db.commit()
            except Exception as e:
                logger.error(e)
                # 如果发生错误则回滚
                db.rollback()


        if 'hhad' in datas[key]:
            type2 = datas[key]['hhad']['type']
            num2 = datas[key]['hhad']['num']
            mid2 = datas[key]['hhad']['mid']
            win2 = datas[key]['hhad']['win']
            lose2 = datas[key]['hhad']['lose']
            draw2 = datas[key]['hhad']['draw']
            total2 = datas[key]['hhad']['total']

            hsql = """INSERT INTO tb_vote_rate
              (`type`, `num`, `mid`, `win`, `lose`, `draw`, `total`)
              VALUES ('%s','%s','%s','%s','%s','%s','%s')""" % (type2, num2, mid2, win2, lose2, draw2, total2)

            try:
                logger.info('execue SQL:%s' % hsql)
                # 执行sql语句
                cursor.execute(hsql)
                # 提交到数据库执行
                db.commit()
            except Exception as e:
                logger.error(e)
                # 如果发生错误则回滚
                db.rollback()

    db.close()
    logger.info('end crawl')


if __name__ == '__main__':
    logger.info('start crawl')
    main()
